﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Common;
using System.Data.SqlClient;
using System.Data;

namespace NVR.DALSqlProvider
{
    public class DALNVRDeviceInfoSqlProvider:IDAL.IDALNVRDeviceInfo
    {
        #region IDALNVRDeviceInfo 成员

        public int Insert(NVR.Entity.NVRDeviceInfo obj)
        {
            Database db = DatabaseFactory.CreateDatabase();
            var cmd = db.GetStoredProcCommand("proc_NVRDeviceInfo_insert") as SqlCommand;
            cmd.Parameters.AddWithValue("@deviceSN", obj.deviceSN == String.Empty ? DBNull.Value : (object)obj.deviceSN);
            cmd.Parameters.AddWithValue("@deviceAdmin", obj.deviceAdmin == String.Empty ? DBNull.Value : (object)obj.deviceAdmin);
            cmd.Parameters.AddWithValue("@devicePwd", obj.devicePwd == String.Empty ? DBNull.Value : (object)obj.devicePwd);
            cmd.Parameters.AddWithValue("@deviceLoc", obj.deviceLoc == String.Empty ? DBNull.Value : (object)obj.deviceLoc);
            cmd.Parameters.AddWithValue("@username", obj.username == String.Empty ? DBNull.Value : (object)obj.username);
            cmd.Parameters.AddWithValue("@addDate",obj.addDate);
            return db.ExecuteNonQuery(cmd);
        }

        public int Update(NVR.Entity.NVRDeviceInfo obj)
        {
            Database db = DatabaseFactory.CreateDatabase();
            var cmd = db.GetStoredProcCommand("proc_NVRDeviceInfo_update") as SqlCommand;
            cmd.Parameters.AddWithValue("@deviceSN", obj.deviceSN == String.Empty ? DBNull.Value : (object)obj.deviceSN);
            cmd.Parameters.AddWithValue("@deviceAdmin", obj.deviceAdmin == String.Empty ? DBNull.Value : (object)obj.deviceAdmin);
            cmd.Parameters.AddWithValue("@devicePwd", obj.devicePwd == String.Empty ? DBNull.Value : (object)obj.devicePwd);
            cmd.Parameters.AddWithValue("@deviceLoc", obj.deviceLoc == String.Empty ? DBNull.Value : (object)obj.deviceLoc);
            cmd.Parameters.AddWithValue("@username", obj.username == String.Empty ? DBNull.Value : (object)obj.username);
            cmd.Parameters.AddWithValue("@addDate", obj.addDate == DateTime.Now ? DBNull.Value : (object)obj.addDate);
            return db.ExecuteNonQuery(cmd);
        }

        public int Delete(NVR.Entity.NVRDeviceInfo obj)
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_NVRDeviceInfo_delete") as SqlCommand;

            //3.cmd参数赋值
            cmd.Parameters.AddWithValue("@deviceSN", obj.deviceSN);
            //4.重要:通过db来执行这个cmd (在db内部对cmd进行构造)
            return db.ExecuteNonQuery(cmd);
        }

        public List<NVR.Entity.NVRDeviceInfo> LoadAll()
        {
            Database db = DatabaseFactory.CreateDatabase();
            string strSql = "select *from NVRDeviceInfo";
            var cmd = db.GetSqlStringCommand(strSql) as SqlCommand;
            var reader = cmd.ExecuteReader();
            List<NVR.Entity.NVRDeviceInfo> list = new List<NVR.Entity.NVRDeviceInfo>();
            while (reader.Read())
            {
                NVR.Entity.NVRDeviceInfo nvr = new NVR.Entity.NVRDeviceInfo();
                nvr.deviceSN = reader["deviceSN"] is DBNull ? String.Empty : (String)reader["deviceSN"];
                nvr.deviceAdmin = reader["deviceAdmin"] is DBNull ? String.Empty : (String)reader["deviceAdmin"];
                nvr.devicePwd = reader["devicePwd"] is DBNull ? String.Empty : (String)reader["devicePwd"];
                nvr.deviceLoc = reader["deviceLoc"] is DBNull ? String.Empty : (String)reader["deviceLoc"];
                nvr.username = reader["username"] is DBNull ? String.Empty : (String)reader["username"];
                nvr.addDate = reader["addDate"] is DBNull ? DateTime.Now : (DateTime)reader["addDate"];
                list.Add(nvr);
            }
            return list;   
        }

        public NVR.Entity.NVRDeviceInfo LoadById(string deviceSN)
        {
            Database db = DatabaseFactory.CreateDatabase();
            var cmd = db.GetStoredProcCommand("proc_NVRDeviceInfo_selectByID") as SqlCommand;
            cmd.Parameters.AddWithValue("@deviceSN", deviceSN);
            var reader = db.ExecuteReader(cmd);
            Entity.NVRDeviceInfo entity = null;

            while (reader.Read())
            {
                entity = new Entity.NVRDeviceInfo();
                entity.deviceSN = reader["deviceSN"] is DBNull ? String.Empty : (String)reader["deviceSN"];
                entity.deviceAdmin = reader["deviceAdmin"] is DBNull ? String.Empty : (String)reader["deviceAdmin"];
                entity.devicePwd = reader["devicePwd"] is DBNull ? String.Empty : (String)reader["devicePwd"];
                entity.deviceLoc = reader["deviceLoc"] is DBNull ? String.Empty : (String)reader["deviceLoc"];
                entity.username = reader["username"] is DBNull ? String.Empty : (String)reader["username"];
                entity.addDate = reader["addDate"] is DBNull ? DateTime.Now : (DateTime)reader["addDate"];
            }
            return entity;
        }
        #endregion
        #region IDALNVRDeviceInfo 成员


        public List<NVR.Entity.NVRDeviceInfo> LoadDVRInfoAndDevInfo(string username)
        {
            Database db = DatabaseFactory.CreateDatabase();
            var cmd = db.GetStoredProcCommand("proc_NVRDeviceInfoAndDeviceInfo_select") as SqlCommand;
            cmd.Parameters.AddWithValue("@username", username);
            var reader = db.ExecuteReader(cmd);
            List<NVR.Entity.NVRDeviceInfo> list = new List<NVR.Entity.NVRDeviceInfo>();
            while (reader.Read())
            {
                NVR.Entity.NVRDeviceInfo nvr = new NVR.Entity.NVRDeviceInfo();
                nvr.deviceSN = reader["deviceSN"] is DBNull ? String.Empty : (String)reader["deviceSN"];
                //nvr.deviceAdmin = reader["deviceAdmin"] is DBNull ? String.Empty : (String)reader["deviceAdmin"];
               // nvr.devicePwd = reader["devicePwd"] is DBNull ? String.Empty : (String)reader["devicePwd"];
                nvr.deviceLoc = reader["deviceLoc"] is DBNull ? String.Empty : (String)reader["deviceLoc"];
                nvr.username = reader["username"] is DBNull ? String.Empty : (String)reader["username"];
               // nvr.addDate = reader["addDate"] is DBNull ? DateTime.Now : (DateTime)reader["addDate"];
                list.Add(nvr);
            }
            return list;
        }
        #endregion

        #region IDALNVRDeviceInfo 成员


        public List<NVR.Entity.NVRDeviceInfo> LoadByUserName(string username)
        {
            {
                List<NVR.Entity.NVRDeviceInfo> list = new List<NVR.Entity.NVRDeviceInfo>();
                Database db = DatabaseFactory.CreateDatabase();
                var cmd = db.GetStoredProcCommand("proc_NVRDeviceInfo_selectByUserName") as SqlCommand;
                cmd.Parameters.AddWithValue("@username", username);
                var reader = db.ExecuteReader(cmd);
                Entity.NVRDeviceInfo entity = null;
                while (reader.Read())
                {
                    entity = new Entity.NVRDeviceInfo();
                    entity.deviceSN = reader["deviceSN"] is DBNull ? String.Empty : (String)reader["deviceSN"];
                    entity.deviceAdmin = reader["deviceAdmin"] is DBNull ? String.Empty : (String)reader["deviceAdmin"];
                    entity.devicePwd = reader["devicePwd"] is DBNull ? String.Empty : (String)reader["devicePwd"];
                    entity.deviceLoc = reader["deviceLoc"] is DBNull ? String.Empty : (String)reader["deviceLoc"];
                    entity.username = reader["username"] is DBNull ? String.Empty : (String)reader["username"];
                    entity.addDate = reader["addDate"] is DBNull ? DateTime.Now : (DateTime)reader["addDate"];
                    list.Add(entity);
                }
                return list;
            }
        }
        #endregion



        #region IDALNVRDeviceInfo 成员

/// <summary>
/// 分页处理数据，有可能该用户下没有一台设备
/// </summary>
/// <param name="currentPage"></param>
/// <param name="pageSize"></param>
/// <param name="counts"></param>
/// <param name="pageNums"></param>
/// <param name="username"></param>
/// <returns></returns>
        public List<NVR.Entity.NVRDeviceInfo> PagePartFunc(int currentPage, int pageSize, out int counts, out int pageNums, string username)
        {
            {
                List<Entity.NVRDeviceInfo> list = new List<Entity.NVRDeviceInfo>();
                Database db = DatabaseFactory.CreateDatabase();
                //exec proc_paged_part_select 'NVRDeviceInfo','*', 10, 1, 'deviceSN', 0,"username='13830'",'deviceSN',0
                var cmd = db.GetStoredProcCommand("proc_paged_part_select") as SqlCommand;
                cmd.Parameters.AddWithValue("@tblName", "NVRDeviceInfo");
                cmd.Parameters.AddWithValue("@fldName", "*");
                cmd.Parameters.AddWithValue("@pageSize", pageSize);
                cmd.Parameters.AddWithValue("@page", currentPage);
                cmd.Parameters.AddWithValue("@fldSort", "addDate"); //要排序的字段
                cmd.Parameters.AddWithValue("@Sort", 1);  //0 升序，1：降序
                cmd.Parameters.AddWithValue("@strCondition", "username='" + username+"'");
                cmd.Parameters.AddWithValue("@ID", "Sn");
                cmd.Parameters.AddWithValue("@Dist", 0);
                cmd.Parameters.Add("@pageCount", SqlDbType.Int).Direction = ParameterDirection.Output;
                cmd.Parameters.Add("@Counts", SqlDbType.Int).Direction = ParameterDirection.Output;
                db.ExecuteNonQuery(cmd);
                counts = (int)cmd.Parameters["@Counts"].Value;
                pageNums = (int)cmd.Parameters["@pageCount"].Value;

                var reader = db.ExecuteReader(cmd);
                while (reader.Read())
                {
                    NVR.Entity.NVRDeviceInfo devInfo = new NVR.Entity.NVRDeviceInfo();
                    devInfo.deviceSN = reader["deviceSN"] is DBNull ? String.Empty : (String)reader["deviceSN"];
                    devInfo.deviceAdmin = reader["deviceAdmin"] is DBNull ? String.Empty : (String)reader["deviceAdmin"];
                    
                    devInfo.devicePwd = reader["devicePwd"] is DBNull ? String.Empty : (String)reader["devicePwd"];
                    devInfo.addDate = reader["addDate"] is DBNull ? DateTime.Now : (DateTime)reader["addDate"];
                    devInfo.username = reader["username"] is DBNull ? String.Empty : (String)reader["username"];
                    devInfo.deviceLoc = reader["deviceLoc"] is DBNull ? String.Empty : (String)reader["deviceLoc"];
                    list.Add(devInfo);
                }
                return list;
            }
        }
        #endregion

        #region IDALNVRDeviceInfo 成员


        public int GetNvrNums(string username)
        {
            Database db = DatabaseFactory.CreateDatabase();
            var cmd = db.GetStoredProcCommand("proc_NVRDeviceInfo_NvrNumByUserName") as SqlCommand;
            cmd.Parameters.AddWithValue("@username", username);
            int nums =(int)db.ExecuteScalar(cmd);
            return nums;
        }
        #endregion
    }
}
